﻿using System.Text;
using System.Collections.ObjectModel;
using MarginTrading.Entity;
using MarginTrading.Common;
using System.Data.OracleClient;
using System.Data;

namespace MarginTrading.Services
{
    public class PortfolioServices
    {
        private static Portfolio GetPortfolioFromDataReader(IDataReader dataReader)
        {
            Portfolio retVal = null;
            if (dataReader != null && !dataReader.IsClosed)
            {
                retVal = new Portfolio();
                retVal.Code = dataReader["CODE"].ToString();
                retVal.Symbol = dataReader["SYMBOL"].ToString();
                int quantity = 0;
                if (int.TryParse(dataReader["QUANTITY"].ToString(), out quantity))
                    retVal.Quantity = quantity;
                double avgValue = 0;
                if (double.TryParse(dataReader["AVG_VALUE"].ToString(), out avgValue))
                    retVal.AvgValue = avgValue;
            }
            return retVal;
        }

        public static Portfolio GetPortfolioByCodeAndSymbol(string code, string symbol)
        {
            Portfolio retVal = null;
            OracleConnection con = new OracleConnection();

            try
            {
                con.ConnectionString = Constants.DB_CONNECTION_STRING;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "select * from portfolio where code = '" + code + "' and symbol = '" + symbol + "'";
                cmd.Connection = con;

                using (IDataReader dataReader = cmd.ExecuteReader())
                {
                    if (dataReader.Read())
                    {
                        retVal = GetPortfolioFromDataReader(dataReader);
                    }
                }
            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
            return retVal;
        }

        public static void Truncate()
        {
            OracleConnection con = new OracleConnection();
            try
            {
                con.ConnectionString = Constants.DB_CONNECTION_STRING;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "truncate table portfolio";
                cmd.Connection = con;

                cmd.ExecuteNonQuery();
            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
        }


        public static PortfolioCollection GetPortfolioCollection()
        {
            PortfolioCollection retVal = null;
            OracleConnection con = new OracleConnection();

            try
            {
                con.ConnectionString = Constants.DB_CONNECTION_STRING;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "select * from portfolio";
                cmd.Connection = con;

                using (IDataReader dataReader = cmd.ExecuteReader())
                {
                    retVal = new PortfolioCollection();
                    while (dataReader.Read())
                    {
                        Portfolio item = GetPortfolioFromDataReader(dataReader);
                        if (item != null)
                            retVal.Add(item);
                    }
                }
            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
            return retVal;
        }

        public static PortfolioCollection GetPortfolioCollectionByCode(string code)
        {
            PortfolioCollection retVal = null;
            OracleConnection con = new OracleConnection();

            try
            {
                con.ConnectionString = Constants.DB_CONNECTION_STRING;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "select * from portfolio where code = '" + code +"'"; //RR
                cmd.Connection = con;

                using (IDataReader dataReader = cmd.ExecuteReader())
                {
                    retVal = new PortfolioCollection();
                    while (dataReader.Read())
                    {
                        Portfolio item = GetPortfolioFromDataReader(dataReader);
                        if (item != null)
                            retVal.Add(item);
                    }
                }
            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
            return retVal;
        }

        public static DataTable GetPortfolioCollectionDataTable()
        {
            DataTable retVal = null;
            OracleConnection con = new OracleConnection();

            try
            {
                con.ConnectionString = Constants.DB_CONNECTION_STRING;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "select * from portfolio";
                cmd.Connection = con;

                using (IDataReader dataReader = cmd.ExecuteReader())
                {
                    if (dataReader != null)
                    {
                        retVal = new DataTable();
                        retVal.Load(dataReader);
                    }
                }
            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
            return retVal;
        }

        public static DataTable GetPortfolioCollectionDataTableByCode(string code)
        {
            DataTable retVal = null;
            OracleConnection con = new OracleConnection();

            try
            {
                con.ConnectionString = Constants.DB_CONNECTION_STRING;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "select * from portfolio where code = " + code;
                cmd.Connection = con;

                using (IDataReader dataReader = cmd.ExecuteReader())
                {
                    if (dataReader != null)
                    {
                        retVal = new DataTable();
                        retVal.Load(dataReader);
                    }
                }
            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
            return retVal;
        }

        public static DataTable GetPortfolioCollectionDataTableByCodeAndSymbol(string code, string symbol)
        {
            DataTable retVal = null;
            OracleConnection con = new OracleConnection();

            string sSql;
            if (code == "" && symbol == "Any")
            {
                sSql = "select * from portfolio";
            }
            else if (code == "" && symbol != "Any")
            {
                sSql = "select * from portfolio where SYMBOL = '" + symbol + "'";
            }
            else if (code != "" && symbol == "Any")
            {
                sSql = "select * from portfolio where CODE = '" + code + "'";
            }
            else
            {
                sSql = "select * from portfolio where CODE = '" + code + "' and SYMBOL = '" + symbol + "'";
            }

            try
            {
                con.ConnectionString = Constants.DB_CONNECTION_STRING;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = sSql;
                cmd.Connection = con;

                using (IDataReader dataReader = cmd.ExecuteReader())
                {
                    if (dataReader != null)
                    {
                        retVal = new DataTable();
                        retVal.Load(dataReader);
                    }
                }
            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
            return retVal;
        }

        public static void Add(Portfolio portfolio)
        {
            OracleConnection con = new OracleConnection();

            try
            {
                con.ConnectionString = Constants.DB_CONNECTION_STRING;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "insert into portfolio(CODE,SYMBOL,QUANTITY,AVG_VALUE)" +
                                  "values(:v1,:v2,:v3,:v4)";
                cmd.Parameters.Add(":v1", OracleType.VarChar).Value = portfolio.Code;
                cmd.Parameters.Add(":v2", OracleType.VarChar).Value = portfolio.Symbol;
                cmd.Parameters.Add(":v3", OracleType.Number).Value = portfolio.Quantity;
                cmd.Parameters.Add(":v4", OracleType.Number).Value = portfolio.AvgValue;

                cmd.Connection = con;
                cmd.ExecuteNonQuery();

            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
        }

        public static void Delete(string code, string symbol)
        {
            OracleConnection con = new OracleConnection();
            try
            {
                con.ConnectionString = Constants.DB_CONNECTION_STRING;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "delete from portfolio where code = '" + code + "' and symbol = '" + symbol +"'"; //Ravin
                cmd.Connection = con;

                cmd.ExecuteNonQuery();
            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
        }

        public static void Update(Portfolio portfolio)
        {
            OracleConnection con = new OracleConnection();

            try
            {
                con.ConnectionString = Constants.DB_CONNECTION_STRING;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "update portfolio set " +
                                  "QUANTITY = :v3, " +
                                  "AVG_VALUE = :v4 " +
                                  "where CODE = :v1 and SYMBOL = :v2";
                cmd.Parameters.Add(":v1", OracleType.VarChar).Value = portfolio.Code;
                cmd.Parameters.Add(":v2", OracleType.VarChar).Value = portfolio.Symbol;
                cmd.Parameters.Add(":v3", OracleType.Number).Value = portfolio.Quantity;
                cmd.Parameters.Add(":v4", OracleType.Number).Value = portfolio.AvgValue;

                cmd.Connection = con;
                cmd.ExecuteNonQuery();

            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
        }
    }
}
